Reference
Default database in this chapter is “ap”.
Other databases included in the simulation MySQL server:
Join Logic
Illustration of common join types:
inner join
left join
right join
outer join
union (“stacking”)
full (outer) join
Note: full join is not available for all SQL dialects. It is a combination of left join + union + right join
Source
Some other join types:
(Left) Semi-Join: returns only rows in the left table that have matching rows in the right table
(Left) Anti-Join: returns rows in the left table that have no matching rows in the right table
Note:
Semi-join and Anti-join don’t bring any additional fields from other tables.
Semi-join and Anti-join are not covered in details here, because MySQL doesn’t have native implementation for these join types.
Inner Join
tables overview
SELECT *
FROM invoices
ORDER BY invoice_number
LIMIT 10
10 records
39
110
0-2058
2022-05-28
37966.19
37966.19
0.00
3
2022-06-27
2022-06-30
106
110
0-2060
2022-07-24
23517.58
21221.63
2295.95
3
2022-08-23
2022-08-27
112
110
0-2436
2022-07-31
10976.06
0.00
0.00
3
2022-08-30
NA
30
123
1-200-5164
2022-05-20
63.40
63.40
0.00
3
2022-06-19
2022-06-24
48
123
1-202-2978
2022-06-03
33.00
33.00
0.00
3
2022-07-03
2022-07-05
21
119
10843
2022-05-11
4901.26
4901.26
0.00
2
2022-05-31
2022-05-29
74
102
109596
2022-06-24
41.80
41.80
0.00
4
2022-08-03
2022-08-04
53
95
111-92R-10092
2022-06-09
46.21
46.21
0.00
2
2022-06-29
2022-07-02
83
95
111-92R-10093
2022-07-06
39.77
39.77
0.00
2
2022-07-26
2022-07-22
44
95
111-92R-10094
2022-06-01
19.67
19.67
0.00
2
2022-06-21
2022-06-24
SELECT *
FROM vendors
ORDER BY vendor_name
LIMIT 10
10 records
94
Abbey Office Furnishings
4150 W Shaw Ave
NA
Fresno
CA
93722
(559) 555-8300
Francis
Kyra
2
150
61
American Booksellers Assoc
828 S Broadway
NA
Tarrytown
NY
10591
(800) 555-0037
Angelica
Nashalie
3
574
98
American Express
Box 0001
NA
Los Angeles
CA
90096
(800) 555-3344
Story
Kirsten
2
160
15
ASC Signs
1528 N Sierra Vista
NA
Fresno
CA
93703
NA
Darien
Elisabeth
1
546
66
Ascom Hasler Mailing Systems
PO Box 895
NA
Shelton
CT
06484
NA
Lewis
Darnell
3
532
93
AT&T
PO Box 78225
NA
Phoenix
AZ
85062
NA
Wesley
Alisha
3
522
52
Aztek Label
Accounts Payable
1150 N Tustin Ave
Anaheim
CA
92807
(714) 555-9000
Griffin
Brian
3
551
47
Baker & Taylor Books
Five Lakepointe Plaza, Ste 500
2709 Water Ridge Parkway
Charlotte
NC
28217
(704) 555-3500
Bernardo
Brittnee
3
572
99
Bertelsmann Industry Svcs. Inc
28210 N Avenue Stanford
NA
Valencia
CA
91355
(805) 555-0584
Potter
Lance
3
400
8
BFI Industries
PO Box 9369
NA
Fresno
CA
93792
(559) 555-1551
Kaleigh
Erick
3
521
Explicit (INNER JOIN)
SELECT invoice_number, vendor_name
FROM vendors INNER JOIN invoices
ON vendors.vendor_id = invoices.vendor_id
ORDER BY invoice_number
LIMIT 10
10 records
0-2058
Malloy Lithographing Inc
0-2060
Malloy Lithographing Inc
0-2436
Malloy Lithographing Inc
1-200-5164
Federal Express Corporation
1-202-2978
Federal Express Corporation
10843
Yesmed, Inc
109596
Coffee Break Service
111-92R-10092
Pacific Bell
111-92R-10093
Pacific Bell
111-92R-10094
Pacific Bell
Compare to the above:
table aliases now used
just JOIN, instead of full INNER JOIN, now used
SELECT invoice_number, vendor_name, invoice_due_date,
invoice_total - payment_total - credit_total
AS balance_due
FROM vendors v JOIN invoices i
ON v.vendor_id = i.vendor_id
WHERE invoice_total - payment_total - credit_total > 0
ORDER BY invoice_due_date DESC
11 records
547480102
Blue Cross
2022-08-31
224.00
0-2436
Malloy Lithographing Inc
2022-08-30
10976.06
9982771
Ford Motor Credit Company
2022-08-23
503.20
P-0608
Malloy Lithographing Inc
2022-08-22
19351.18
263253270
Federal Express Corporation
2022-08-21
67.92
263253273
Federal Express Corporation
2022-08-21
30.75
263253268
Federal Express Corporation
2022-08-20
59.97
963253264
Federal Express Corporation
2022-08-17
52.25
134116
Cardinal Business Media, Inc.
2022-08-17
90.36
31361833
Ingram
2022-08-10
579.42
39104
Data Reproductions Corp
2022-08-09
85.31
Compare to the above:
alias now refers to a table from a different database
SELECT vendor_name, customer_last_name,
customer_first_name, vendor_state AS state,
vendor_city AS city
FROM vendors v
JOIN om.customers c
ON v.vendor_zip_code = c.customer_zip
ORDER BY state, city
Displaying records 1 - 15
Wells Fargo Bank
Marissa
Kyle
AZ
Phoenix
Aztek Label
Irvin
Ania
CA
Anaheim
Zylka Design
Neftaly
Thalia
CA
Fresno
Lou Gentile’s Flower Basket
Damien
Deborah
CA
Fresno
Costco
Holbrooke
Rashad
CA
Fresno
Costco
Neftaly
Thalia
CA
Fresno
Shields Design
Damien
Deborah
CA
Fresno
Wakefield Co
Neftaly
Thalia
CA
Fresno
Wakefield Co
Holbrooke
Rashad
CA
Fresno
Gary McKeighan Insurance
Holbrooke
Rashad
CA
Fresno
Gary McKeighan Insurance
Neftaly
Thalia
CA
Fresno
Digital Dreamworks
Holbrooke
Rashad
CA
Fresno
Digital Dreamworks
Neftaly
Thalia
CA
Fresno
Gostanian General Building
Holbrooke
Rashad
CA
Fresno
Gostanian General Building
Neftaly
Thalia
CA
Fresno
Implicit joins (WHERE clause instead of ON clause)
SELECT invoice_number, vendor_name
FROM vendors v, invoices i
WHERE v.vendor_id = i.vendor_id
ORDER BY invoice_number
LIMIT 10
10 records
0-2058
Malloy Lithographing Inc
0-2060
Malloy Lithographing Inc
0-2436
Malloy Lithographing Inc
1-200-5164
Federal Express Corporation
1-202-2978
Federal Express Corporation
10843
Yesmed, Inc
109596
Coffee Break Service
111-92R-10092
Pacific Bell
111-92R-10093
Pacific Bell
111-92R-10094
Pacific Bell
Implicit joins with (USING keyword), results identical as the above.
SELECT invoice_number, vendor_name
FROM vendors
JOIN invoices USING (vendor_id)
ORDER BY invoice_number
Displaying records 1 - 15
0-2058
Malloy Lithographing Inc
0-2060
Malloy Lithographing Inc
0-2436
Malloy Lithographing Inc
1-200-5164
Federal Express Corporation
1-202-2978
Federal Express Corporation
10843
Yesmed, Inc
109596
Coffee Break Service
111-92R-10092
Pacific Bell
111-92R-10093
Pacific Bell
111-92R-10094
Pacific Bell
111-92R-10095
Pacific Bell
111-92R-10096
Pacific Bell
111-92R-10097
Pacific Bell
111897
Suburban Propane
121897
Gostanian General Building
Implicit joins with (USING keyword), results identical as the above.
use inner join
use columns of same names from the tables
MySQL, yes, but not for all SQL dialects
SELECT invoice_number, vendor_name
FROM vendors
NATURAL JOIN invoices
ORDER BY invoice_number
Displaying records 1 - 15
0-2058
Malloy Lithographing Inc
0-2060
Malloy Lithographing Inc
0-2436
Malloy Lithographing Inc
1-200-5164
Federal Express Corporation
1-202-2978
Federal Express Corporation
10843
Yesmed, Inc
109596
Coffee Break Service
111-92R-10092
Pacific Bell
111-92R-10093
Pacific Bell
111-92R-10094
Pacific Bell
111-92R-10095
Pacific Bell
111-92R-10096
Pacific Bell
111-92R-10097
Pacific Bell
111897
Suburban Propane
121897
Gostanian General Building
Join on multiple columns (“combo join”, join based on combination of columns/keys)
table customers overview:
SELECT *
FROM om.customers
Displaying records 1 - 15
1
Korah
Blanca
1555 W Lane Ave
Columbus
OH
43221
6145554435
6145553928
2
Yash
Randall
11 E Rancho Madera Rd
Madison
WI
53707
2095551205
2095552262
3
Johnathon
Millerton
60 Madison Ave
New York
NY
10010
2125554800
NULL
4
Mikayla
Damion
2021 K Street Nw
Washington
DC
20006
2025555561
NULL
5
Kendall
Mayte
4775 E Miami River Rd
Cleves
OH
45002
5135553043
NULL
6
Kaitlin
Hostlery
3250 Spring Grove Ave
Cincinnati
OH
45225
8005551957
8005552826
7
Derek
Chaddick
9022 E Merchant Wy
Fairfield
IA
52556
5155556130
NULL
8
Deborah
Damien
415 E Olive Ave
Fresno
CA
93728
5595558060
NULL
9
Karina
Lacy
882 W Easton Wy
Los Angeles
CA
90084
8005557000
NULL
10
Kurt
Nickalus
28210 N Avenue Stanford
Valencia
CA
91355
8055550584
055556689
11
Kelsey
Eulalia
7833 N Ridge Rd
Sacramento
CA
95887
2095557500
2095551302
12
Anders
Rohansen
12345 E 67th Ave NW
Takoma Park
MD
24512
3385556772
NULL
13
Thalia
Neftaly
2508 W Shaw Ave
Fresno
CA
93711
5595556245
NULL
14
Gonzalo
Keeton
12 Daniel Road
Fairfield
NJ
07004
2015559742
NULL
15
Ania
Irvin
1099 N Farcourt St
Orange
CA
92807
7145559000
NULL
SELECT *
FROM ex.employees
9 records
1
Smith
Cindy
2
NA
2
Jones
Elmer
4
1
3
Simonian
Ralph
2
2
4
Hernandez
Olivia
1
9
5
Aaronsen
Robert
2
4
6
Watson
Denise
6
8
7
Hardy
Thomas
5
2
8
O’Leary
Rhea
4
9
9
Locario
Paulo
6
1
“Are there any customers and employees with the same first and last name?”
SELECT customer_first_name, customer_last_name
FROM om.customers c INNER JOIN ex.employees e
ON c.customer_first_name = e.first_name
AND c.customer_last_name = e.last_name
A Somewhat Special Example - Self Join
To make self-join work:
must use alias to avoid recycling same table reference
“In those cities where more than 1 vendors reside, what are the OTHER vendors that in the same city and state?”
Explore the vendor table:
SELECT *
FROM vendors v1
ORDER BY v1.vendor_id, v1.vendor_state, v1.vendor_city
Displaying records 1 - 15
1
US Postal Service
Attn: Supt. Window Services
PO Box 7005
Madison
WI
53707
(800) 555-1205
Alberto
Francesco
1
552
2
National Information Data Ctr
PO Box 96621
NA
Washington
DC
20120
(301) 555-8950
Irvin
Ania
3
540
3
Register of Copyrights
Library Of Congress
NA
Washington
DC
20559
NA
Liana
Lukas
3
403
4
Jobtrak
1990 Westwood Blvd Ste 260
NA
Los Angeles
CA
90025
(800) 555-8725
Quinn
Kenzie
3
572
5
Newbrige Book Clubs
3000 Cindel Drive
NA
Washington
NJ
07882
(800) 555-9980
Marks
Michelle
4
394
6
California Chamber Of Commerce
3255 Ramos Cir
NA
Sacramento
CA
95827
(916) 555-6670
Mauro
Anton
3
572
7
Towne Advertiser’s Mailing Svcs
Kevin Minder
3441 W Macarthur Blvd
Santa Ana
CA
92704
NA
Maegen
Ted
3
540
8
BFI Industries
PO Box 9369
NA
Fresno
CA
93792
(559) 555-1551
Kaleigh
Erick
3
521
9
Pacific Gas & Electric
Box 52001
NA
San Francisco
CA
94152
(800) 555-6081
Anthoni
Kaitlyn
3
521
10
Robbins Mobile Lock And Key
4669 N Fresno
NA
Fresno
CA
93726
(559) 555-9375
Leigh
Bill
2
523
11
Bill Marvin Electric Inc
4583 E Home
NA
Fresno
CA
93703
(559) 555-5106
Hostlery
Kaitlin
2
523
12
City Of Fresno
PO Box 2069
NA
Fresno
CA
93718
(559) 555-9999
Mayte
Kendall
3
574
13
Golden Eagle Insurance Co
PO Box 85826
NA
San Diego
CA
92186
NA
Blanca
Korah
3
590
14
Expedata Inc
4420 N. First Street, Suite 108
NA
Fresno
CA
93726
(559) 555-9586
Quintin
Marvin
3
589
15
ASC Signs
1528 N Sierra Vista
NA
Fresno
CA
93703
NA
Darien
Elisabeth
1
546
Step 1: simple self-joining based on city and state of vendor
SELECT *
FROM vendors v1 INNER JOIN vendors v2
ON v1.vendor_city = v2.vendor_city AND
v1.vendor_state = v2.vendor_state
ORDER BY v1.vendor_id, v1.vendor_state, v1.vendor_city
Displaying records 1 - 15
1
US Postal Service
Attn: Supt. Window Services
PO Box 7005
Madison
WI
53707
(800) 555-1205
Alberto
Francesco
1
552
1
US Postal Service
Attn: Supt. Window Services
PO Box 7005
Madison
WI
53707
(800) 555-1205
Alberto
Francesco
1
552
2
National Information Data Ctr
PO Box 96621
NA
Washington
DC
20120
(301) 555-8950
Irvin
Ania
3
540
2
National Information Data Ctr
PO Box 96621
NA
Washington
DC
20120
(301) 555-8950
Irvin
Ania
3
540
2
National Information Data Ctr
PO Box 96621
NA
Washington
DC
20120
(301) 555-8950
Irvin
Ania
3
540
3
Register of Copyrights
Library Of Congress
NA
Washington
DC
20559
NA
Liana
Lukas
3
403
2
National Information Data Ctr
PO Box 96621
NA
Washington
DC
20120
(301) 555-8950
Irvin
Ania
3
540
82
Reiter’s Scientific & Pro Books
2021 K Street Nw
NA
Washington
DC
20006
(202) 555-5561
Rodolfo
Carlee
2
572
3
Register of Copyrights
Library Of Congress
NA
Washington
DC
20559
NA
Liana
Lukas
3
403
82
Reiter’s Scientific & Pro Books
2021 K Street Nw
NA
Washington
DC
20006
(202) 555-5561
Rodolfo
Carlee
2
572
3
Register of Copyrights
Library Of Congress
NA
Washington
DC
20559
NA
Liana
Lukas
3
403
2
National Information Data Ctr
PO Box 96621
NA
Washington
DC
20120
(301) 555-8950
Irvin
Ania
3
540
3
Register of Copyrights
Library Of Congress
NA
Washington
DC
20559
NA
Liana
Lukas
3
403
3
Register of Copyrights
Library Of Congress
NA
Washington
DC
20559
NA
Liana
Lukas
3
403
4
Jobtrak
1990 Westwood Blvd Ste 260
NA
Los Angeles
CA
90025
(800) 555-8725
Quinn
Kenzie
3
572
42
Opamp Technical Books
1033 N Sycamore Ave.
NA
Los Angeles
CA
90038
(213) 555-4322
Paris
Gideon
3
572
4
Jobtrak
1990 Westwood Blvd Ste 260
NA
Los Angeles
CA
90025
(800) 555-8725
Quinn
Kenzie
3
572
4
Jobtrak
1990 Westwood Blvd Ste 260
NA
Los Angeles
CA
90025
(800) 555-8725
Quinn
Kenzie
3
572
4
Jobtrak
1990 Westwood Blvd Ste 260
NA
Los Angeles
CA
90025
(800) 555-8725
Quinn
Kenzie
3
572
106
Ford Motor Credit Company
Dept 0419
NA
Los Angeles
CA
90084
(800) 555-7000
Snyder
Karen
3
582
4
Jobtrak
1990 Westwood Blvd Ste 260
NA
Los Angeles
CA
90025
(800) 555-8725
Quinn
Kenzie
3
572
112
Office Depot
File No 81901
NA
Los Angeles
CA
90074
(800) 555-1711
Pinsippi
Val
3
570
4
Jobtrak
1990 Westwood Blvd Ste 260
NA
Los Angeles
CA
90025
(800) 555-8725
Quinn
Kenzie
3
572
98
American Express
Box 0001
NA
Los Angeles
CA
90096
(800) 555-3344
Story
Kirsten
2
160
5
Newbrige Book Clubs
3000 Cindel Drive
NA
Washington
NJ
07882
(800) 555-9980
Marks
Michelle
4
394
5
Newbrige Book Clubs
3000 Cindel Drive
NA
Washington
NJ
07882
(800) 555-9980
Marks
Michelle
4
394
6
California Chamber Of Commerce
3255 Ramos Cir
NA
Sacramento
CA
95827
(916) 555-6670
Mauro
Anton
3
572
67
Bill Jones
Secretary Of State
PO Box 944230
Sacramento
CA
94244
NA
Deasia
Tristin
3
589
6
California Chamber Of Commerce
3255 Ramos Cir
NA
Sacramento
CA
95827
(916) 555-6670
Mauro
Anton
3
572
77
State Board Of Equalization
PO Box 942808
NA
Sacramento
CA
94208
(916) 555-4911
Dean
Julissa
1
631
Unequal join to remove the vendor itself, so that the rest of the vendors are left in the result:
SELECT *
FROM vendors v1 INNER JOIN vendors v2
ON v1.vendor_city = v2.vendor_city AND
v1.vendor_state = v2.vendor_state AND
v1.vendor_name <> v2.vendor_name
ORDER BY v1.vendor_id, v1.vendor_state, v1.vendor_city
Displaying records 1 - 15
2
National Information Data Ctr
PO Box 96621
NA
Washington
DC
20120
(301) 555-8950
Irvin
Ania
3
540
82
Reiter’s Scientific & Pro Books
2021 K Street Nw
NA
Washington
DC
20006
(202) 555-5561
Rodolfo
Carlee
2
572
2
National Information Data Ctr
PO Box 96621
NA
Washington
DC
20120
(301) 555-8950
Irvin
Ania
3
540
3
Register of Copyrights
Library Of Congress
NA
Washington
DC
20559
NA
Liana
Lukas
3
403
3
Register of Copyrights
Library Of Congress
NA
Washington
DC
20559
NA
Liana
Lukas
3
403
2
National Information Data Ctr
PO Box 96621
NA
Washington
DC
20120
(301) 555-8950
Irvin
Ania
3
540
3
Register of Copyrights
Library Of Congress
NA
Washington
DC
20559
NA
Liana
Lukas
3
403
82
Reiter’s Scientific & Pro Books
2021 K Street Nw
NA
Washington
DC
20006
(202) 555-5561
Rodolfo
Carlee
2
572
4
Jobtrak
1990 Westwood Blvd Ste 260
NA
Los Angeles
CA
90025
(800) 555-8725
Quinn
Kenzie
3
572
42
Opamp Technical Books
1033 N Sycamore Ave.
NA
Los Angeles
CA
90038
(213) 555-4322
Paris
Gideon
3
572
4
Jobtrak
1990 Westwood Blvd Ste 260
NA
Los Angeles
CA
90025
(800) 555-8725
Quinn
Kenzie
3
572
98
American Express
Box 0001
NA
Los Angeles
CA
90096
(800) 555-3344
Story
Kirsten
2
160
4
Jobtrak
1990 Westwood Blvd Ste 260
NA
Los Angeles
CA
90025
(800) 555-8725
Quinn
Kenzie
3
572
112
Office Depot
File No 81901
NA
Los Angeles
CA
90074
(800) 555-1711
Pinsippi
Val
3
570
4
Jobtrak
1990 Westwood Blvd Ste 260
NA
Los Angeles
CA
90025
(800) 555-8725
Quinn
Kenzie
3
572
106
Ford Motor Credit Company
Dept 0419
NA
Los Angeles
CA
90084
(800) 555-7000
Snyder
Karen
3
582
6
California Chamber Of Commerce
3255 Ramos Cir
NA
Sacramento
CA
95827
(916) 555-6670
Mauro
Anton
3
572
95
Pacific Bell
NA
NA
Sacramento
CA
95887
(209) 555-7500
Nickalus
Kurt
2
522
6
California Chamber Of Commerce
3255 Ramos Cir
NA
Sacramento
CA
95827
(916) 555-6670
Mauro
Anton
3
572
116
State of California
Employment Development Dept
PO Box 826276
Sacramento
CA
94230
(209) 555-5132
Articunia
Mercedez
1
631
6
California Chamber Of Commerce
3255 Ramos Cir
NA
Sacramento
CA
95827
(916) 555-6670
Mauro
Anton
3
572
107
Franchise Tax Board
PO Box 942857
NA
Sacramento
CA
94257
NA
Prado
Anita
4
507
6
California Chamber Of Commerce
3255 Ramos Cir
NA
Sacramento
CA
95827
(916) 555-6670
Mauro
Anton
3
572
87
DMV Renewal
PO Box 942894
NA
Sacramento
CA
94294
NA
Josey
Lorena
4
568
6
California Chamber Of Commerce
3255 Ramos Cir
NA
Sacramento
CA
95827
(916) 555-6670
Mauro
Anton
3
572
77
State Board Of Equalization
PO Box 942808
NA
Sacramento
CA
94208
(916) 555-4911
Dean
Julissa
1
631
6
California Chamber Of Commerce
3255 Ramos Cir
NA
Sacramento
CA
95827
(916) 555-6670
Mauro
Anton
3
572
67
Bill Jones
Secretary Of State
PO Box 944230
Sacramento
CA
94244
NA
Deasia
Tristin
3
589
8
BFI Industries
PO Box 9369
NA
Fresno
CA
93792
(559) 555-1551
Kaleigh
Erick
3
521
63
Lou Gentile’s Flower Basket
722 E Olive Ave
NA
Fresno
CA
93728
(559) 555-6643
Anum
Trisha
1
570
Take distinct values of what’s left in the “left table”. These are the other vendors in the same cities and states.
SELECT DISTINCT v1.vendor_name, v1.vendor_city,
v1.vendor_state
FROM vendors v1 INNER JOIN vendors v2
ON v1.vendor_city = v2.vendor_city AND
v1.vendor_state = v2.vendor_state AND
v1.vendor_name <> v2.vendor_name
ORDER BY v1.vendor_state, v1.vendor_city
Displaying records 1 - 15
Computer Library
Phoenix
AZ
Wells Fargo Bank
Phoenix
AZ
AT&T
Phoenix
AZ
Aztek Label
Anaheim
CA
Blue Shield of California
Anaheim
CA
Shields Design
Fresno
CA
Graylift
Fresno
CA
ASC Signs
Fresno
CA
Internal Revenue Service
Fresno
CA
Fresno Photoengraving Company
Fresno
CA
Costco
Fresno
CA
Crown Printing
Fresno
CA
Yale Industrial Trucks-Fresno
Fresno
CA
Zylka Design
Fresno
CA
Dataforms/West
Fresno
CA
Outer Join
This includes:
left join
right join
full outer join
tables overview
SELECT *
FROM ex.departments
LIMIT 10
5 records
1
Accounting
2
Payroll
3
Operations
4
Personnel
5
Maintenance
SELECT *
FROM ex.employees
LIMIT 10
9 records
1
Smith
Cindy
2
NA
2
Jones
Elmer
4
1
3
Simonian
Ralph
2
2
4
Hernandez
Olivia
1
9
5
Aaronsen
Robert
2
4
6
Watson
Denise
6
8
7
Hardy
Thomas
5
2
8
O’Leary
Rhea
4
9
9
Locario
Paulo
6
1
SELECT *
FROM ex.projects
LIMIT 10
8 records
P1011
8
P1011
4
P1012
3
P1012
1
P1012
5
P1013
6
P1013
9
P1014
10
Left Join
SELECT department_name, last_name, project_number
FROM ex.departments d
LEFT JOIN ex.employees e
ON d.department_number = e.department_number
LEFT JOIN ex.projects p
ON e.employee_id = p.employee_id
ORDER BY department_name, last_name
8 records
Accounting
Hernandez
P1011
Maintenance
Hardy
NA
Operations
NA
NA
Payroll
Aaronsen
P1012
Payroll
Simonian
P1012
Payroll
Smith
P1012
Personnel
Jones
NA
Personnel
O’Leary
P1011
Right Join
SELECT department_name, e.department_number, last_name
FROM ex.departments d
RIGHT JOIN ex.employees e
ON d.department_number = e.department_number
ORDER BY department_name
9 records
NA
6
Watson
NA
6
Locario
Accounting
1
Hernandez
Maintenance
5
Hardy
Payroll
2
Smith
Payroll
2
Simonian
Payroll
2
Aaronsen
Personnel
4
Jones
Personnel
4
O’Leary
combine different join types:
SELECT department_name, last_name, project_number
FROM ex.departments d
INNER JOIN ex.employees e
ON d.department_number = e.department_number
LEFT JOIN ex.projects p
ON e.employee_id = p.employee_id
ORDER BY department_name, last_name
7 records
Accounting
Hernandez
P1011
Maintenance
Hardy
NA
Payroll
Aaronsen
P1012
Payroll
Simonian
P1012
Payroll
Smith
P1012
Personnel
Jones
NA
Personnel
O’Leary
P1011
Cross Join
It creates all combinations of rows from different tables.
tables overview:
SELECT *
FROM ex.departments
LIMIT 10
5 records
1
Accounting
2
Payroll
3
Operations
4
Personnel
5
Maintenance
SELECT *
FROM ex.employees
LIMIT 10
9 records
1
Smith
Cindy
2
NA
2
Jones
Elmer
4
1
3
Simonian
Ralph
2
2
4
Hernandez
Olivia
1
9
5
Aaronsen
Robert
2
4
6
Watson
Denise
6
8
7
Hardy
Thomas
5
2
8
O’Leary
Rhea
4
9
9
Locario
Paulo
6
1
cross join:
SELECT departments.department_number, department_name,
employee_id, last_name
FROM ex.departments CROSS JOIN ex.employees
ORDER BY departments.department_number
Displaying records 1 - 15
1
Accounting
9
Locario
1
Accounting
1
Smith
1
Accounting
8
O’Leary
1
Accounting
4
Hernandez
1
Accounting
2
Jones
1
Accounting
7
Hardy
1
Accounting
5
Aaronsen
1
Accounting
3
Simonian
1
Accounting
6
Watson
2
Payroll
9
Locario
2
Payroll
4
Hernandez
2
Payroll
1
Smith
2
Payroll
8
O’Leary
2
Payroll
2
Jones
2
Payroll
7
Hardy
Union
This is essentially stacking different intermediate table results one on top of another:
Each result set must return the same number of columns.
The corresponding columns in each result set must have compatible data types.
The column names in the final result set are taken from the first SELECT clause.
SELECT 'Active' AS source ,
invoice_number,
invoice_date,
invoice_total
FROM ex.active_invoices
WHERE invoice_date >= '2022-06-01'
UNION
SELECT 'Paid' AS source ,
invoice_number,
invoice_date,
invoice_total
FROM ex.paid_invoices
WHERE invoice_date >= '2022-06-01'
ORDER BY invoice_total DESC
Displaying records 1 - 15
Active
40318
2022-07-18
21842.00
Paid
P02-3772
2022-06-03
7125.34
Paid
10843
2022-06-04
4901.26
Paid
77290
2022-06-04
1750.00
Paid
RTR-72-3662-X
2022-06-04
1600.00
Paid
75C-90227
2022-06-06
1367.50
Paid
P02-88D77S7
2022-06-06
856.92
Active
I77271-O01
2022-06-05
662.00
Active
9982771
2022-06-03
503.20
Paid
121897
2022-06-01
450.00
Paid
CBM9920-M-T77109
2022-06-07
290.00
Paid
133560
2022-06-01
175.00
Active
134116
2022-06-01
90.36
Active
39104
2022-06-20
85.31
Active
111-92R-10092
2022-06-04
46.21
Full Outer Join
A combination of left outer join and right outer join.
There is no keywords in MySQL to do full outer join directly. Other SQL dialects might have (e.g. T-SQL has FULL (OUTER) JOIN)
in MySQL it is literally a UNION of LEFT JOIN set and RIGHT JOIN set
SELECT department_name AS dept_name,
d.department_number AS d_dept_no,
e.department_number AS e_dept_no, last_name
FROM ex.departments d
LEFT JOIN ex.employees e
ON d.department_number = e.department_number
UNION
SELECT department_name AS dept_name,
d.department_number AS d_dept_no,
e.department_number AS e_dept_no, last_name
FROM ex.departments d
RIGHT JOIN ex.employees e
ON d.department_number = e.department_number
ORDER BY dept_name
10 records
NA
NA
6
Watson
NA
NA
6
Locario
Accounting
1
1
Hernandez
Maintenance
5
5
Hardy
Operations
3
NA
NA
Payroll
2
2
Aaronsen
Payroll
2
2
Simonian
Payroll
2
2
Smith
Personnel
4
4
O’Leary
Personnel
4
4
Jones